package com.test.excel.excel.controller.utils;

import com.sun.org.apache.bcel.internal.generic.NEW;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.format.annotation.DateTimeFormat;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author wxb
 * @Datatime 2021/7/29 11:56
 * @Descript 解析excel
 */
@Slf4j
public class ExcelUtils {

    /**
     * 解析excel
     */
    public static void readExcel(String filePath) throws ParseException {
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        List<Map<String,String>> list = null;
        wb = getWorkbook(filePath);
        //获取第一sheet
        if(wb!=null){
            sheet = wb.getSheetAt(0);
        }
        //获取最大行数
        int rowNum = sheet.getPhysicalNumberOfRows();
        log.info("rowNum:{}",rowNum);
        //获取第一行
        row = sheet.getRow(0);
//        log.info("第一行:{}",row);
        //获取最大列数
        int columnNum = row.getPhysicalNumberOfCells();
        log.info("最大列数:{}",columnNum);
        list = new ArrayList<Map<String,String>>();
        //循环解析每一列
        Cell cell = null;
        for(int i=1;i<rowNum;i++){
            Map<String,String> map = new LinkedHashMap<>();
            row = sheet.getRow(i);
            if(row!=null){
                for(int j=0;j<columnNum;j++){
                    log.info("j:{}:{}",j,row.getCell(j));
                    switch (j){
                        case 3:
                        case 10:
                        case 11:
                            cell = row.getCell(j);
                            cell.setCellType((CellType.STRING));
                            log.info(cell+"");
                            break;
                        case 7:
                        case 12:
                            cell = row.getCell(j);
                            log.info("日期:{}",DateUtils.formateDate(cell.toString()));
                            break;
                        default:
                            log.info(row.getCell(j)+"");
                    }
                }
            }else{
                break;
            }
            list.add(map);
        }
    }

    /**
     * 读取excel
     * @param filePath
     * @return
     */
    public static Workbook getWorkbook(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if(".xls".equals(extString)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                wb = new XSSFWorkbook(is);
            }else{
                return null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

}
